using Microsoft.Data.Sqlite;
using Microsoft.UI.Xaml;
using Windows.Storage;
namespace Devtodo
{
  public partial class App : Application
  {
    private Window _window;
    public App()
    {
      var connection = new SqliteConnection("Data Source=" + ApplicationData.Current.LocalFolder.Path + "/devtodo.db");
      connection.Open();
      var createProjectCmd = new SqliteCommand("CREATE TABLE IF NOT EXISTS project (id INTEGER PRIMARY KEY AUTOINCREMENT, name UNIQUE)", connection);
      createProjectCmd.ExecuteNonQuery();
      var createVersionCmd = new SqliteCommand("CREATE TABLE IF NOT EXISTS version (id INTEGER PRIMARY KEY AUTOINCREMENT, projectid, version, UNIQUE (projectid, version))", connection);
      createVersionCmd.ExecuteNonQuery();
      var createUpdateCmd = new SqliteCommand("CREATE TABLE IF NOT EXISTS 'update' (id INTEGER PRIMARY KEY AUTOINCREMENT, versionid, type, content, tip, finished, UNIQUE (versionid, content))", connection);
      createUpdateCmd.ExecuteNonQuery();
      var createLogCmd = new SqliteCommand("CREATE TABLE IF NOT EXISTS log (id INTEGER PRIMARY KEY AUTOINCREMENT, 'table', type, time)", connection);
      createLogCmd.ExecuteNonQuery();
      var createIndexProjectNameCmd = new SqliteCommand("CREATE INDEX IF NOT EXISTS name ON project(name)", connection);
      createIndexProjectNameCmd.ExecuteNonQuery();
      var createIndexVersionProjectIdCmd = new SqliteCommand("CREATE INDEX IF NOT EXISTS projectid ON version(projectid)", connection);
      createIndexVersionProjectIdCmd.ExecuteNonQuery();
      var createIndexUpdateVersionIdCmd = new SqliteCommand("CREATE INDEX IF NOT EXISTS versionid ON 'update'(versionid, type, finished)", connection);
      createIndexUpdateVersionIdCmd.ExecuteNonQuery();
      var triggerProjectInsert = new SqliteCommand("CREATE TRIGGER IF NOT EXISTS ProjectAfterInsert AFTER INSERT ON project FOR EACH ROW BEGIN INSERT INTO log ('table', type, time) VALUES ('项目', '新增', datetime('now')); END;", connection);
      triggerProjectInsert.ExecuteNonQuery();
      var triggerProjectUpdate = new SqliteCommand("CREATE TRIGGER IF NOT EXISTS ProjectAfterUpdate AFTER Update ON project FOR EACH ROW BEGIN INSERT INTO log ('table', type, time) VALUES ('项目', '修改', datetime('now')); END;", connection);
      triggerProjectUpdate.ExecuteNonQuery();
      var triggerProjectDelete = new SqliteCommand("CREATE TRIGGER IF NOT EXISTS ProjectAfterDelete AFTER Delete ON project FOR EACH ROW BEGIN INSERT INTO log ('table', type, time) VALUES ('项目', '删除', datetime('now')); END;", connection);
      triggerProjectDelete.ExecuteNonQuery();
      var triggerVersionInsert = new SqliteCommand("CREATE TRIGGER IF NOT EXISTS VersionAfterInsert AFTER INSERT ON version FOR EACH ROW BEGIN INSERT INTO log ('table', type, time) VALUES ('版本', '新增', datetime('now')); END;", connection);
      triggerVersionInsert.ExecuteNonQuery();
      var triggerVersionUpdate = new SqliteCommand("CREATE TRIGGER IF NOT EXISTS VersionAfterUpdate AFTER Update ON version FOR EACH ROW BEGIN INSERT INTO log ('table', type, time) VALUES ('版本', '修改', datetime('now')); END;", connection);
      triggerVersionUpdate.ExecuteNonQuery();
      var triggerVersionDelete = new SqliteCommand("CREATE TRIGGER IF NOT EXISTS VersionAfterDelete AFTER Delete ON version FOR EACH ROW BEGIN INSERT INTO log ('table', type, time) VALUES ('版本', '删除', datetime('now')); END;", connection);
      triggerVersionDelete.ExecuteNonQuery();
      var triggerUpdateInsert = new SqliteCommand("CREATE TRIGGER IF NOT EXISTS UpdateAfterInsert AFTER INSERT ON 'update' FOR EACH ROW BEGIN INSERT INTO log ('table', type, time) VALUES ('更新', '新增', datetime('now')); END;", connection);
      triggerUpdateInsert.ExecuteNonQuery();
      var triggerUpdateUpdate = new SqliteCommand("CREATE TRIGGER IF NOT EXISTS UpdateAfterUpdate AFTER Update ON 'update' FOR EACH ROW BEGIN INSERT INTO log ('table', type, time) VALUES ('更新', '修改', datetime('now')); END;", connection);
      triggerUpdateUpdate.ExecuteNonQuery();
      var triggerUpdateDelete = new SqliteCommand("CREATE TRIGGER IF NOT EXISTS UpdateAfterDelete AFTER Delete ON 'update' FOR EACH ROW BEGIN INSERT INTO log ('table', type, time) VALUES ('更新', '删除', datetime('now')); END;", connection);
      triggerUpdateDelete.ExecuteNonQuery();
      connection.Close();
      InitializeComponent();
    }
    protected override void OnLaunched(LaunchActivatedEventArgs args)
    {
      _window = new MainWindow();
      _window.Activate();
    }
  }
}